package com.addplus.townmall.server.api.mapper.town;

import com.addplus.townmall.server.api.model.town.ShopProduct;
import com.addplus.townmall.server.api.model.town.expand.ShopProductDescribeExpand;
import com.addplus.townmall.server.api.model.town.expand.ShopProductExpand;
import com.addplus.townmall.server.api.model.town.expand.SolrModelExpand;
import com.addplus.townmall.server.api.model.town.param.AddShopProductParam;
import com.addplus.townmall.server.api.model.town.param.OrderShopItemsParam;
import com.addplus.townmall.server.api.utils.BaseAddMapper;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 商品信息表 Mapper 接口
 * </p>
 *
 * @author zhangjiehang
 * @since 2018-08-27
 */
public interface ShopProductMapper extends BaseAddMapper<ShopProduct> {

    @Select("SELECT\n" +
            " s.id,\n" +
            " s.shop_code,\n" +
            " s.shop_name,\n" +
            " s.shop_share_describe,\n" +
            " s.shop_imgs,\n" +
            " s.shop_seal_img,\n" +
            " s.store_id,\n" +
            " s.shop_category_id,\n" +
            " s.shop_describe,\n" +
            " s.shop_video,\n" +
            " s.sale_price,\n" +
            " s.line_price,\n" +
            " s.unit,\n" +
            " s.stock,\n" +
            " s.is_discount,\n" +
            " s.shop_customize_code,\n" +
            " s.sale_status,\n" +
            " s.online_status,\n" +
            " s.visit_count+s.vistit_virtual_count as visit_count,\n" +
            " s.sale_count+s.sale_virtual_count as sale_count,\n" +
            " s.shop_basic_gurarantee_path\n" +
            "FROM shop_product s WHERE  s.audit_status =0 AND s.is_deleted=0 AND s.id = #{shopProductId}")
    @Results({
            @Result(column = "id", property = "shopProduct.id"),
            @Result(column = "shop_code", property = "shopProduct.shopCode"),
            @Result(column = "shop_name", property = "shopProduct.shopName"),
            @Result(column = "shop_share_describe", property = "shopProduct.shopShareDescribe"),
            @Result(column = "shop_imgs", property = "shopProduct.shopImgs"),
            @Result(column = "shop_seal_img", property = "shopProduct.shopSealImg"),
            @Result(column = "store_id", property = "shopProduct.storeId"),
            @Result(column = "shop_category_id", property = "shopProduct.shopCategoryId"),
            @Result(column = "shop_describe", property = "shopProduct.shopDescribe"),
            @Result(column = "shop_video", property = "shopProduct.shopVideo"),
            @Result(column = "sale_price", property = "shopProduct.salePrice"),
            @Result(column = "line_price", property = "shopProduct.linePrice"),
            @Result(column = "unit", property = "shopProduct.unit"),
            @Result(column = "stock", property = "shopProduct.stock"),
            @Result(column = "is_discount", property = "shopProduct.isDiscount"),
            @Result(column = "shop_customize_code", property = "shopProduct.shopCustomizeCode"),
            @Result(column = "sale_status", property = "shopProduct.saleStatus"),
            @Result(column = "online_status", property = "shopProduct.onlineStatus"),
            @Result(column = "visit_count", property = "shopProduct.visitCount"),
            @Result(column = "sale_count", property = "shopProduct.saleCount"),
            @Result(column = "shop_basic_gurarantee_path", property = "shopProduct.shopBasicGuraranteePath")
    })
    ShopProductDescribeExpand getShopProductContext(@Param("shopProductId") Integer shopProductId);

    @Select("SELECT p.id,p.shop_name,p.shop_seal_img,p.shop_describe,p.sale_price,p.line_price,p.sale_count+p.sale_virtual_count as sale_count ," +
            "sd.shop_goods , sd.shop_comment_count " +
            "FROM shop_product p " +
            "LEFT JOIN shop_product_describe sd ON sd.shop_product_id = p.id " +
            " WHERE p.store_id = #{store_id} AND p.is_deleted = 0 AND p.audit_status=0 AND p.sale_status = 0 " +
            "ORDER BY p.sort")
    List<ShopProduct> getStoreShopProductByPage(Page page, @Param("store_id") Integer storeId);

    @Select("<script>\n" +
            "SELECT\n" +
            " sp.id,\n" +
            " sp.store_id,\n" +
            " sp.shop_name,\n" +
            " sp.shop_describe,\n" +
            " sp.shop_seal_img,\n" +
            " sp.sale_price,\n" +
            " sp.line_price,\n" +
            " sp.sale_count + sp.sale_virtual_count as sale_count, \n" +
            "sd.shop_goods ,\n" +
            "sd.shop_comment_count \n" +
            "FROM\n" +
            " shop_product sp\n" +
            " LEFT JOIN shop_product_describe sd ON sp.id = sd.shop_product_id\n" +
            " AND sd.is_deleted = 0\n" +
            "WHERE\n" +
            " sp.is_deleted = 0\n" +
            " AND sp.online_status = 1\n" +
            "AND sp.store_id = #{storeId} " +
            "<if test=\"overallRateSort != null or saleSort != null or serviceRateSort != null or priceSort != null \">\n" +
            "ORDER BY\n" +
            "</if> \n" +
            "    <if test=\"overallRateSort != null \">\n" +
            "        sd.shop_product_overall_rate \n" +
            "        <if test=\"overallRateSort == 0\">\n" +
            "            ASC \n" +
            "        </if>\n" +
            "        <if test=\"overallRateSort == 1\">\n" +
            "            DESC \n" +
            "        </if>\n" +
            "        <if test=\"saleSort != null or serviceRateSort != null or priceSort != null \">\n" +
            "            ,\n" +
            "        </if>\n" +
            "    </if>\n" +
            "    <if test=\"saleSort != null \">\n" +
            "        sp.sale_count \n" +
            "        <if test=\"saleSort == 0\">\n" +
            "            ASC\n" +
            "        </if>\n" +
            "        <if test=\"saleSort == 1\">\n" +
            "            DESC\n" +
            "        </if>\n" +
            "        <if test=\"serviceRateSort != null or priceSort != null \">\n" +
            "            ,\n" +
            "        </if>\n" +
            "    </if>\n" +
            "    <if test=\"serviceRateSort != null \">\n" +
            "        sd.shop_product_service_rate \n" +
            "        <if test=\"serviceRateSort == 0\">\n" +
            "            ASC\n" +
            "        </if>\n" +
            "        <if test=\"serviceRateSort == 1\">\n" +
            "            DESC\n" +
            "        </if>\n" +
            "        <if test=\"priceSort != null \">\n" +
            "            ,\n" +
            "        </if>\n" +
            "    </if>\n" +
            "    <if test=\"priceSort != null \">\n" +
            "         sp.sale_price \n" +
            "        <if test=\"priceSort == 0\">\n" +
            "            ASC\n" +
            "        </if>\n" +
            "        <if test=\"priceSort == 1\">\n" +
            "            DESC\n" +
            "        </if>\n" +
            "    </if>\n" +
            "</script>")
    List<ShopProduct> getStoreShopProductByPages(Page page, @Param("overallRateSort") Integer overallRateSort,
                                                 @Param("saleSort") Integer saleSort,
                                                 @Param("serviceRateSort") Integer serviceRateSort,
                                                 @Param("priceSort") Integer priceSort, @Param("storeId") Integer storeId);

    @Select("SELECT p.id,p.shop_name,p.shop_seal_img,p.shop_describe,p.cost_price,p.line_price,p.sale_count+p.sale_virtual_count as sale_count FROM shop_product p LEFT JOIN store s ON s.id = p.store_id AND s.store_type = 0 AND s.is_delete=0\n" +
            "WHERE p.shop_type_id in #{platformList} AND p.is_deleted = 0 AND p.audit_status=0 AND p.sale_status = 0\n" +
            "ORDER BY p.sort")
    IPage<ShopProduct> getPersonShopProductByCateGoryId(Page page, @Param("platformList") List<Integer> platformList);


    @Select("<script> " +
            "SELECT p.store_id, p.id as shop_product_id,p.shop_name,p.shop_seal_img,p.shop_fright_id,p.fright_type,p.fright,p.is_discount,p.online_status,p.sale_status,\n" +
            "p.shop_customize_code,sku.id as shop_specification_sku_id,sku.sku_describe as shop_specification_sku_json," +
            "sku.sale_price,sku.cost_price,sku.line_price,sku.stock," +
            " p.shop_tag,\n" +
            " a.shop_active_type,\n" +
            " a.shop_obj_id,\n" +
            " a.status \n" +
            "FROM shop_product p \n" +
            "LEFT JOIN shop_specification_sku sku ON sku.shop_product_id = p.id AND sku.is_deleted = 0\n" +
            " LEFT JOIN shop_participate_active a ON sku.shop_product_id = a.shop_product_id \n" +
            " AND a.shop_sku_id = sku.id \n" +
            " AND a.`status` = 1 \n" +
            " AND a.is_deleted = 0\n" +
            "WHERE \n" +
            "sku.id in " +
            "<foreach collection='shopSpecificationSkuId' open='('  item='item'  separator=',' close=')'> #{item}</foreach> " +
            "AND p.audit_status = 0 AND p.is_deleted = 0 " +
            "AND sku.type = 1 " +
            "ORDER BY sku.id,a.shop_obj_id desc " +
            "</script>")
    List<OrderShopItemsParam> getShopProductOrderMessage(@Param("shopSpecificationSkuId") List<Integer> shopSpecificationSkuId);


    @Select("SELECT p.store_id, p.id as shop_product_id,p.shop_name,p.shop_seal_img,p.shop_fright_id,p.fright_type,p.is_discount,\n" +
            "p.shop_customize_code,sku.id as shop_specification_sku_id,sku.sku_describe as shop_specification_sku_json,\n" +
            "sku.sale_price,sku.cost_price,sku.line_price as shop_price,sku.stock,\n" +
            "IFNULL(c.enable_noreason_return,0) as enable_noreason_return \n" +
            "FROM shop_product p \n" +
            "LEFT JOIN shop_specification_sku sku ON sku.shop_product_id = p.id AND sku.is_deleted = 0\n" +
            "LEFT JOIN platform_category c ON p.shop_category_id = c.id AND c.is_deleted = 0\n" +
            "WHERE\n" +
            "sku.id = #{shop_specification_sku_id} AND p.id = #{shop_product_id}\n" +
            "AND p.audit_status = 0 AND p.is_deleted = 0 AND p.sale_status = 0 ")
    OrderShopItemsParam getShopProductOrderMessageSingle(@Param("shop_specification_sku_id") Integer shopSpecificationSkuId, @Param("shop_product_id") Integer shopProductId);

    @Select("select s.*, sd.shop_product_describe from shop_product s left join shop_product_describe as sd on s.id = sd.shop_product_id where s.id = #{id}")
    AddShopProductParam getShopProductById(Integer id);

    @Select("select s.* from shop_product s left join store on s.store_id = store.id where s.is_deleted=0 AND s.shop_name LIKE concat('%',#{shop_name},'%') AND store.store_name LIKE concat('%',#{store_name},'%')")
    List<ShopProduct> getShopProductByStoreNameOrShopName(IPage page, @Param("shop_name") String shop_name, @Param("store_name") String store_name);

    @Select("select s.store_name,sp.*,spd.shop_comment_count,sku.sku_id as sku_id,sku.id as shop_sku_id " +
            "from store as s " +
            "inner JOIN shop_product as sp ON s.id = sp.store_id " +
            "inner JOIN shop_product_describe spd ON sp.id = spd.shop_product_id " +
            "inner JOIN shop_specification_sku sku ON sku.shop_product_id = sp.id " +
            "where ${ew.sqlSegment}")
    List<ShopProductExpand> getSelfShopProduct(Page page, @Param("ew") Wrapper wrapper);

    @Select("select s.store_name,sp.*,spd.shop_comment_count,sku.sku_id as sku_id,sku.id as shop_sku_id " +
            "from shop_product  sp " +
            "inner JOIN shop_product_describe spd ON sp.id = spd.shop_product_id " +
            "inner JOIN store  s ON s.id = sp.store_id " +
            "inner JOIN shop_specification_sku sku ON sku.shop_product_id = sp.id " +
            "where ${ew.sqlSegment}")
    List<ShopProductExpand> getVipShopProduct(Page page, @Param("ew") Wrapper wrapper);


    @Select("<script>\n" +
            "SELECT\n" +
            " p.id,\n" +
            " p.store_id,\n" +
            " p.shop_name,\n" +
            " p.shop_describe,\n" +
            " p.shop_seal_img,\n" +
            " p.sale_price,\n" +
            " p.line_price,\n" +
            " s.id AS store_id,\n" +
            " s.store_name,\n" +
            " s.icon_path,\n" +
            " d.good_rate as shop_goods,\n" +
            " d.total_comment\n" +
            "FROM\n" +
            " shop_product p\n" +
            " LEFT JOIN store s ON s.id = p.store_id\n" +
            " AND s.store_status = 1\n" +
            " AND s.is_delete = 0\n" +
            " LEFT JOIN shop_comment_all d ON d.shop_product_id = p.id\n" +
            " AND d.is_deleted = 0\n" +
            " LEFT JOIN shop_product_describe sd ON sd.shop_product_id = p.id\n" +
            " AND sd.is_deleted = 0\n" +
            "WHERE\n" +
            " p.is_deleted = 0\n" +
            " AND p.online_status = 1\n" +
            " AND p.is_discount = 1\n" +
            "<if test=\"overallRateSort != null or saleSort != null or serviceRateSort != null or priceSort != null \">\n" +
            "ORDER BY\n" +
            "</if> \n" +
            "    <if test=\"overallRateSort != null \">\n" +
            "        sd.shop_product_overall_rate \n" +
            "        <if test=\"overallRateSort == 0\">\n" +
            "            ASC \n" +
            "        </if>\n" +
            "        <if test=\"overallRateSort == 1\">\n" +
            "            DESC \n" +
            "        </if>\n" +
            "        <if test=\"saleSort != null or serviceRateSort != null or priceSort != null \">\n" +
            "            ,\n" +
            "        </if>\n" +
            "    </if>\n" +
            "    <if test=\"saleSort != null \">\n" +
            "        p.sale_count \n" +
            "        <if test=\"saleSort == 0\">\n" +
            "            ASC\n" +
            "        </if>\n" +
            "        <if test=\"saleSort == 1\">\n" +
            "            DESC\n" +
            "        </if>\n" +
            "        <if test=\"serviceRateSort != null or priceSort != null \">\n" +
            "            ,\n" +
            "        </if>\n" +
            "    </if>\n" +
            "    <if test=\"serviceRateSort != null \">\n" +
            "        sd.shop_product_service_rate \n" +
            "        <if test=\"serviceRateSort == 0\">\n" +
            "            ASC\n" +
            "        </if>\n" +
            "        <if test=\"serviceRateSort == 1\">\n" +
            "            DESC\n" +
            "        </if>\n" +
            "        <if test=\"priceSort != null \">\n" +
            "            ,\n" +
            "        </if>\n" +
            "    </if>\n" +
            "    <if test=\"priceSort != null \">\n" +
            "         p.sale_price \n" +
            "        <if test=\"priceSort == 0\">\n" +
            "            ASC\n" +
            "        </if>\n" +
            "        <if test=\"priceSort == 1\">\n" +
            "            DESC\n" +
            "        </if>\n" +
            "    </if>\n" +
            "</script>")
    List<ShopProductExpand> getShopProductExclusiveList(Page page, @Param("overallRateSort") Integer overallRateSort,
                                                        @Param("saleSort") Integer saleSort,
                                                        @Param("serviceRateSort") Integer serviceRateSort,
                                                        @Param("priceSort") Integer priceSort);


    @Select("select s.store_name,sp.*" +
            "from shop_product  sp " +
            "inner JOIN store  s ON s.id = sp.store_id " +
            "where is_deleted=0 AND sp.id=#{shop_id} AND sp.stock>0 AND s.store_type=0")
    ShopProductExpand getShopProductByShopId(@Param("shop_id")Integer shop_id);


    @Select("select sp.*  " +
            "from shop_product sp " +
            "left join platform_category pc on  pc.id = sp.shop_category_id " +
            "left join distribution_personal_shop_type dt on dt.shop_type_id = pc.parent_id " +
            "where dt.personal_shop_id=#{storeId} and sp.is_deleted=0 and sp.online_status=1")
    List<ShopProduct> getMemberStoreShopProductByPages(Page<ShopProduct> page, @Param("storeId")Integer storeId);

    @Select("SELECT\n" +
            " p.id,\n" +
            " p.shop_name,\n" +
            " p.shop_describe,\n" +
            " p.shop_seal_img,\n" +
            " p.shop_seal_img_pc,\n" +
            " p.store_id,\n" +
            " p.shop_category_id,\n" +
            " p.shop_tag,\n" +
            " p.sale_price,\n" +
            " p.line_price,\n" +
            " p.online_status,\n" +
            " p.collect_virtual_count,\n" +
            " p.collect_count,\n" +
            " p.visit_count,\n" +
            " p.vistit_virtual_count,\n" +
            " p.sale_count,\n" +
            " p.sale_virtual_count,\n" +
            " p.is_deleted,\n" +
            " p.gmt_create,\n" +
            " d.shop_product_describe,\n" +
            " d.shop_goods,\n" +
            " d.shop_comment_count,\n" +
            " d.shop_product_overall_rate,\n" +
            " d.shop_product_rate,\n" +
            " d.shop_product_service_rate,\n" +
            " d.shop_product_logistics_rate,\n" +
            " a.total_comment,\n" +
            " a.good_comment,\n" +
            " a.middle_comment,\n" +
            " a.bad_comment,\n" +
            " a.good_rate \n" +
            "FROM\n" +
            " shop_product p\n" +
            " LEFT JOIN shop_product_describe d ON d.shop_product_id = p.id \n" +
            " AND d.is_deleted = 0\n" +
            " LEFT JOIN shop_comment_all a ON a.shop_product_id = p.id \n" +
            " AND a.is_deleted = 0 \n" +
            " AND a.type = 1 \n" +
            "WHERE\n" +
            " p.id = #{shopProductId} \n" +
            " AND p.is_deleted = 0")
    SolrModelExpand getSolrModel(@Param("shopProductId") Integer id);

    @Select("SELECT p.id,p.shop_name,p.shop_seal_img_pc,p.sale_price,p.line_price " +
            "FROM shop_product p " +
            " WHERE p.store_id = #{store_id} AND p.is_deleted = 0 AND p.audit_status=0 AND p.sale_status = 0 " +
            "ORDER BY p.sort")
    List<ShopProduct> getStoreShopProductByStoreId(Page page, @Param("store_id") Integer storeId);
}
